from typing import Union

from sqlalchemy import update, CursorResult, text, func
from sqlmodel import select, desc, col, asc
from sqlmodel.sql._expression_select_cls import SelectOfScalar, Select

from src.base.beans import PageResult
from src.config.decorator import data_scope, tenant
from src.config.db import mysqlClient
from src.domain.sys_menu import SysMenu, SysMenuReq
from src.domain.sys_role import SysRole
from src.domain.sys_role_meun import SysRoleMenu
from src.domain.sys_user import SysUser
from src.domain.sys_user_role import SysUserRole


def get_menu_by_id(menu_id: int) -> SysMenu:
    stmt = (
        select(SysMenu)
        .select_from(SysMenu)
        .outerjoin(SysUser, col(SysUser.user_id).__eq__(SysMenu.creator), )
        .where(SysMenu.menu_id == menu_id).where(SysMenu.del_flag == False)
    )
    return mysqlClient.exec(stmt).first()


def where_handle(stmt: Union[Select, SelectOfScalar], entity: SysMenu = None):
    stmt.where(SysMenu.del_flag == False)
    if entity:
        if entity.menu_id:
            stmt = stmt.where(SysMenu.menu_id == entity.menu_id)
        if entity.menu_name:
            stmt = stmt.where(SysMenu.menu_name == entity.menu_name)
        if entity.menu_code:
            stmt = stmt.where(SysMenu.menu_code == entity.menu_code)
        if entity.status:
            stmt = stmt.where(SysMenu.status == entity.status)
    return stmt


def get_menu_list_by_condition(entity: SysMenu = None) -> list[SysMenu]:
    stmt = select(SysMenu)
    stmt = where_handle(stmt, entity)
    # order_by() 是返回一个新的语句对象，而不是原地修改
    stmt = stmt.order_by(desc(SysMenu.sort))
    users = mysqlClient.exec(stmt)
    return users


def get_menu_page_by_condition(entity: SysMenuReq = None) -> PageResult:
    search = SysMenu(**entity.model_dump())
    list_stmt = select(SysMenu)
    list_stmt = where_handle(list_stmt, search)
    if entity.sorter:
        list_stmt = list_stmt.order_by(text(entity.sorter))
    else:
        list_stmt = list_stmt.order_by(asc(SysMenu.sort))
    list_stmt = list_stmt.limit(entity.size).offset(entity.size * (entity.current - 1))
    users = mysqlClient.exec(list_stmt).all()

    total_stmt = select(func.count(SysMenu.user_id))
    total_stmt = where_handle(total_stmt, search)
    total_result = mysqlClient.exec(total_stmt)
    total = total_result.one_or_none()
    return PageResult(total=total, data=users, current=entity.current, size=entity.size)



def insert_menu(entity: SysMenu = None):
    mysqlClient.add(entity)


def update_menu(entity: SysMenu = None) -> int:
    stmt = update(SysMenu).values(entity.model_dump()).where(col(SysMenu.menu_id).is_(entity.menu_id))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


def delete_menu(menu_id: int = None) -> int:
    stmt = update(SysMenu).values(SysMenu(del_flag=True).model_dump()).where(col(SysMenu.menu_id).is_(menu_id))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


def delete_menus(menu_ids: list[int] = None) -> int:
    stmt = update(SysMenu).values(SysMenu(del_flag=True).model_dump()).where(col(SysMenu.menu_id).in_(menu_ids))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


# def get_menu_list_by_role_ids(role_ids: list[int]):
#     menu_sql = text(
#         "select distinct m.* from sys_menu m left join sys_role_menu rm on m.menu_id = rm.menu_id where rm.role_id in :role_ids")
#     menu_stmt = select(SysMenu).from_statement(menu_sql)
#     return mysqlClient.exec(menu_stmt, params={"role_ids": tuple(role_ids)}).scalars().all()

def get_menu_list_by_role_ids(role_ids: list[int]):
    stmt = (select(SysMenu)
            .select_from(SysMenu)
            .outerjoin(SysRoleMenu, col(SysRoleMenu.menu_id).__eq__(SysMenu.menu_id), )
            .where(col(SysRoleMenu.menu_id).in_(role_ids)))
    return mysqlClient.exec(stmt).all()


@tenant("sys_menu", )
@data_scope("sys_user", "sys_menu", user_id_alias="creator")
def get_menu_list_by_user_id(user_id: int):
    stmt = (
        select(SysMenu)
        .select_from(SysMenu)
        .outerjoin(SysRoleMenu, col(SysMenu.menu_id).__eq__(SysRoleMenu.menu_id), )
        .outerjoin(SysRole, col(SysRole.role_id).__eq__(SysRoleMenu.role_id), )
        .outerjoin(SysUserRole, col(SysRole.role_id).__eq__(SysUserRole.role_id))
        .outerjoin(SysUser, col(SysUser.user_id).__eq__(SysUserRole.user_id))
        .where(col(SysUserRole.user_id).__eq__(user_id))
    )
    stmt = stmt.order_by(desc(SysMenu.sort))
    return mysqlClient.exec(stmt).all()
